package data;

import java.sql.Timestamp;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import models.Course;
import models.Room;
import models.Student;
import models.Teacher;
import models.TeachingSession;

import org.sql2o.Query;
import org.sql2o.Sql2o;
import org.sql2o.StatementRunnable;
import org.sql2o.data.Row;
import org.sql2o.data.Table;

public class CourseDAO implements ICourseDAO {
	private static Sql2o database;
	static {
		database = new Connection().open();
	}
	
	public List<Room> getRooms(){
		String sql = "select id, name from Room";
		return database.createQuery(sql).executeAndFetch(Room.class);
	}
	
	public List<Student> listStudents(){
		String sql = "select id, firstName, lastName, personType, registrationStatus from Person where personType = 0";
		return database.createQuery(sql).executeAndFetch(Student.class);
	}
	
	public List<Student> listStudents(int courseId ){
		String sql = "select id, firstName, lastName, personType, registrationStatus from Person where personType = 0 and id in (select studentId from StudentOfTutor where courseId = :courseId)";
		return database.createQuery(sql).addParameter("courseId", courseId).executeAndFetch(Student.class);
	}
	
	public void addStudentsToTutor(final int courseId, final int tutorId, final String[] studentIds){
	    final String sql = "INSERT INTO StudentOfTutor(studentId, tutorId, courseId) VALUES (:studentId, :tutorId, :courseId)";
	    
	    database.runInTransaction(new StatementRunnable() {
			@Override
			public void run(org.sql2o.Connection connection, Object argument)
					throws Throwable {
				connection.createQuery("SET SQL_SAFE_UPDATES=0").executeUpdate();
				
				Query deleteQuery = connection.createQuery("Delete From StudentOfTutor where tutorId = :tutorId and courseId = :courseId");
				
				deleteQuery.addParameter("tutorId", tutorId).addParameter("courseId", courseId).executeUpdate();
	            Query query = connection.createQuery(sql);
	            if(studentIds != null){
	            	for(String id : studentIds){
	            		query.addParameter("studentId", id).addParameter("tutorId", tutorId).addParameter("courseId", courseId).addToBatch();
	            	}
	            }
	            query.executeBatch();		
			}
	    });
	}
	
	public List<Student> getUnassignedStudents(int courseId){
		final String sql = "select id, firstName, lastName, personType, registrationStatus, image from Person p join StudentInCourse sc"
				+" on p.id = sc.studentId where sc.courseId = :courseId and id not in (select studentId from StudentOfTutor where courseId = :courseId)";
		return database.createQuery(sql).addParameter("courseId", courseId).executeAndFetch(Student.class);
	}
	
	public List<Student> getTutorStudents(int courseId, int tutorId){
		final String sql = "select id, firstName, lastName, personType, registrationStatus, image from Person p join StudentOfTutor sc"
				+" on p.id = sc.studentId where sc.courseId = :courseId and sc.tutorId = :tutorId";
		return database.createQuery(sql).addParameter("tutorId", tutorId)
				.addParameter("courseId", courseId).executeAndFetch(Student.class);
	}
	
	
	public List<Teacher> listTeachers(int courseId){
		final String sql = "select id, firstName, lastName, personType from Person where id in (select teacherId from TeacherInCourse where courseId = :courseId)";
		return database.createQuery(sql).addParameter("courseId", courseId).executeAndFetch(Teacher.class);
	}
	
	public List<Teacher> listTutors(int courseId){
		final String sql = "select id, firstName, lastName, personType from Person where id in (select teacherId from TeacherInCourse where courseId = :courseId) and personType = 2";
		return database.createQuery(sql).addParameter("courseId", courseId).executeAndFetch(Teacher.class);
	}
	
	public List<Teacher> listLecturers(int courseId){
		final String sql = "select id, firstName, lastName, personType from Person where id in (select teacherId from TeacherInCourse where courseId = :courseId) and personType = 1";
		return database.createQuery(sql).addParameter("courseId", courseId).executeAndFetch(Teacher.class);
	}
	
	public List<TeachingSession> listTutorSessions(int courseId, int tutorId) {
		final String sql = "select s.id, s.name, s.sessionType, s.date, t.firstName, t.lastName, t.id as 'teacherId', isHavingRecord from TeachingSession s join Person t on s.teacherId = t.id"
				+	" where s.courseId = :courseId and s.teacherId = :tutorId";
			Table table = database.createQuery(sql)
					.addParameter("courseId", courseId)
					.addParameter("tutorId", tutorId)
					.executeAndFetchTable();
			List<TeachingSession> sessions = new LinkedList<>();
			for(Row r : table.rows()){
				TeachingSession s = new TeachingSession();
				Teacher teacher = new Teacher();
				
				s.setId(r.getInteger("id"));
				s.setSessionType(r.getInteger("sessionType"));
				s.setDate(r.getDate("date"));
				s.setName(r.getString("name"));
				s.setIsHavingRecord(r.getInteger("isHavingRecord"));
				teacher.setFirstName(r.getString("firstName"));
				teacher.setLastName(r.getString("lastName"));
				teacher.setId(r.getInteger("teacherId"));
				s.setTeacher(teacher);
				sessions.add(s);
			}
		return sessions;
	}
	
	public List<TeachingSession> listSessions(int courseId){
		final String sql = "select s.id, s.name, s.sessionType, s.date, t.firstName, t.lastName, t.id as 'teacherId', isHavingRecord from TeachingSession s join Person t on s.teacherId = t.id"
				+	" where s.courseId = :courseId";
			Table table = database.createQuery(sql)
					.addParameter("courseId", courseId)
					.executeAndFetchTable();
			List<TeachingSession> sessions = new LinkedList<>();
			for(Row r : table.rows()){
				TeachingSession s = new TeachingSession();
				Teacher teacher = new Teacher();
				
				s.setId(r.getInteger("id"));
				s.setSessionType(r.getInteger("sessionType"));
				s.setDate(r.getDate("date"));
				s.setName(r.getString("name"));
				s.setIsHavingRecord(r.getInteger("isHavingRecord"));
				teacher.setFirstName(r.getString("firstName"));
				teacher.setLastName(r.getString("lastName"));
				teacher.setId(r.getInteger("teacherId"));
				s.setTeacher(teacher);
				sessions.add(s);
			}
		return sessions;
	}
	
	public Course get(int id) {
		final String sql = "select * from Course where id = :id";
		Course course = database.createQuery(sql).addParameter("id", id).executeAndFetchFirst(Course.class);	
		return course;
	}
	
	public Object insertTeachingSession(int courseId, int sessionType, int teacherId, int roomId, Date date, String name ){
		final String sql = "insert into TeachingSession(courseId, sessionType, teacherId, roomId, date, name) values(:courseId, :sessionType, :teacherId, :roomId, :date, :name)";
		Object result = database.createQuery(sql)
			.addParameter("courseId", courseId)
			.addParameter("sessionType", sessionType)
			.addParameter("teacherId", teacherId)
			.addParameter("roomId", roomId)
			.addParameter("date", new Timestamp(date.getTime()))
			.addParameter("name", name)
			.executeUpdate().getKey();
		return result;
	}
	
	public List<Course> list() {
		final String sql = "select * from Course";
		return database.createQuery(sql).executeAndFetch(Course.class);
	}
	
	public List<Course> listTeacherCourses(int teacherId){
		final String sql = "select * from Course where id in (select courseId from TeacherInCourse where teacherId = :teacherId)";
		return database.createQuery(sql).addParameter("teacherId", teacherId).executeAndFetch(Course.class);
	}
	
	public List<Course> listStudentCourses(int studentId){
		final String sql = "select * from Course where id in (select courseId from StudentInCourse where studentId = :studentId)";
		return database.createQuery(sql).addParameter("studentId", studentId).executeAndFetch(Course.class);
	}
}
